Tutorials and examples - How do I ... ?

Preface

This section enlists a set of answers on common How do I ... ? questions related to the generated code and the functionality LLBLGen Pro provides and a list of all the example code inside the various sections in this documentation. The How do I...? examples are given in C# and VB.NET and if required in both template group semantics, and if not, SelfServicing is used. All examples use the Northwind database schema, unless stated otherwise.

Examples in the documentation

Below you'll find an extensive list of all the example snippets located in this documentation. This list is to give you a quick access to code snippets to perform a well defined goal without having to read all the documentation first to locate the examples. It's recommended that you read the paragraph in which the example is located as well, to get more information about when the example is useful. The examples are stored in the order in which they appear in the documentation if you read everything from top to bottom.
Using the designer
Using the generated code, SelfServicing specific
Using the generated code, Adapter specific
Using the generated code, general
Using the generated code, Linq to LLBLGen Pro

How do I ... ?

How do I read all entities of a given type into a collection?

   Selfservicing
Load all customers into a collection
  • C#
  • VB.NET
// C#
CustomerCollection allCustomers = new CustomerCollection();
allCustomers.GetMulti(null);
' VB.NET
Dim allCustomers As New CustomerCollection()
allCustomers.GetMulti(Nothing)

   Adapter
  • C#
  • VB.NET
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection allCustomers = new EntityCollection(new CustomerEntityFactory());
adapter.FetchEntityCollection(allCustomers, null);
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim allCustomers As New EntityCollection(New CustomerEntityFactory())
adapter.FetchEntityCollection(allCustomers, Nothing)

Back to the list
How do I insert a new entity?

   Selfservicing
Inserts a new product entity
  • C#
  • VB.NET
// C#
ProductEntity newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
newProduct.Save();
'VB.NET
Dim newProduct As New ProductEntity()
newProduct.CategoryID=1
newProduct.Discontinued=False;
newProduct.ProductName="TooCool"
newProduct.QuantityPerUnit="1"
newProduct.ReorderLevel=10
newProduct.SupplierID=1
newProduct.UnitPrice=2.13D
newProduct.UnitsInStock = 50
newProduct.UnitsOnOrder=0
newProduct.Save()

   Adapter
(entity is not fetched back)
  • C#
  • VB.NET
// C#
ProductEntity newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
DataAccessAdapter adapter = new DataAccessAdapter();
// if the newProduct object is used after this call, specify True
// for refetchAfterSave, a parameter of SaveEntity()
adapter.SaveEntity(newProduct);
'VB.NET
Dim newProduct As New ProductEntity()
newProduct.CategoryID=1
newProduct.Discontinued=False;
newProduct.ProductName="TooCool"
newProduct.QuantityPerUnit="1"
newProduct.ReorderLevel=10
newProduct.SupplierID=1
newProduct.UnitPrice=2.13D
newProduct.UnitsInStock = 50
newProduct.UnitsOnOrder=0
Dim adapter As New DataAccessAdapter()
' if the newProduct object is used after this call, specify True
' for refetchAfterSave, a parameter of SaveEntity()
adapter.SaveEntity(newProduct)

Back to the list
How do I delete an existing entity?

Method 1: entity is already loaded into memory.

   Selfservicing
Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints)
  • C#
  • VB.NET
// C#
// fetch the entity.
CustomerEntity customer = new CustomerEntity("FISSA");
// ... some other code
customer.Delete();
' VB.NET
' fetch the entity.
Dim customer As New CustomerEntity("FISSA")
' ... some other code
customer.Delete()

   Adapter
  • C#
  • VB.NET
// C#
// fetch the entity.
CustomerEntity customer = new CustomerEntity("FISSA");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
// ... some other code
adapter.DeleteEntity(customer);
' VB.NET
' fetch the entity.
Dim customer As New CustomerEntity("FISSA")
Dim adapter As New DataAccessAdapter()
adapter.FetchEntity(customer)
' ... some other code
adapter.DeleteEntity(customer)

Method 2: use a new entity object to directly delete an entity from the database. Just set the PK fields to a value.

   Selfservicing
Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints)
  • C#
  • VB.NET
// C#
CustomerEntity customer = new CustomerEntity();
customer.CustomerID = "FISSA";
customer.Delete();
' VB.NET
Dim customer As New CustomerEntity()
customer.CustomerID = "FISSA"
customer.Delete()

   Adapter
  • C#
  • VB.NET
// C#
CustomerEntity customer = new CustomerEntity("FISSA");
adapter.DeleteEntity(customer);
' VB.NET
Dim customer As New CustomerEntity("FISSA")
adapter.DeleteEntity(customer)

Method 3: use a predicate expression to delete the entity directly.

   Selfservicing
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(CustomerFields.CustomerID == "FISSA");
CustomerCollection customers = new CustomerCollection();
customers.DeleteMulti(filter);
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareValuePredicate( _
	CustomerFields.CustomerID, ComparisonOperator.Equal, "FISSA"))
Dim customers As New CustomerCollection()
customers.DeleteMulti(filter)

' which is equal to (VB.NET 2005)
Dim customers As New CustomerCollection()
customers.DeleteMulti((CustomerFields.CustomerID="FISSA"))

   Adapter
  • C#
  • VB.NET
// C#
IRelationPredicateBucket bucket = 
	new RelationPredicateBucket((CustomerFields.CustomerID == "FISSA"));
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);
' VB.NET
Dim bucket As New RelationPredicateBucket()
bucket.PredicateExpression.Add(New FieldCompareValuePredicate( _ 
	CustomerFields.CustomerID, Nothing, ComparisonOperator.Equal, "FISSA"))
Dim adapter As New DataAccessAdapter()
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket)

' Which is equal to (VB.NET 2005)
Dim bucket As New RelationPredicateBucket( _
	(CustomerFields.CustomerID = "FISSA"))
Dim adapter As New DataAccessAdapter()
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket)

Back to the list
How do I pass different connection strings at runtime using Adapter?

All generated code (SelfServicing or Adapter) by default reads the connection string from the *.config file of the application referencing the generated code. You can pass a different connection string than the one generated into the app.config file to the DataAccessAdapter's class constructor. That instance will then use the connection string passed in instead of the connection string defined in the *.config file.

// C#
DataAccessAdapter adapter = new DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret");
' VB.NET
Dim adapter As New DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret")

Back to the list
How do I test if an entity is actually loaded from the database?

   Selfservicing
  • C#
  • VB.NET
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim loadedCorrectly As Boolean = (customer.Fields.State = EntityState.Fetched)

   Adapter
  • C#
  • VB.NET
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
bool loadedCorrectly = adapter.FetchEntity(customer);

Or..
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim adapter As New DataAccessAdapter()
Dim loadedCorrectly As Boolean = adapter.FetchEntity(customer)

Or..
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim adapter As New DataAccessAdapter()
adapter.FetchEntity(customer)
Dim loadedCorrectly As Boolean = (customer.Fields.State = EntityState.Fetched)

note Note:
LLBLGen Pro won't change the catalog / schema based on the connectionstring you're specifying. The connection string is used to connect to the database system, though persistence information in the generated code contains the catalog name(s) to target. If you want to modify the catalog to target with the code at runtime, please specify name overwriting settings, as discussed in DataAccessAdapter functionality, for sqlserver and Application configuration through .config files

Back to the list
How can I determine at runtime which fields are the primary key fields of an entity?

References to these fields are stored in the ArrayList returned by the property PrimaryKeyFields of an entity object. You can also walk the field objects in the collection returned by the Fields property of an entity object and check the IsPrimaryKey.

Back to the list
How do I insert a NULL value for a field in a new entity?

When you create an entity object, all fields get a default value. When you save the entity (using the SelfServicing method or the Adapter method), all changed fields are saved, all non-changed fields are skipped. This means, that if you for example save a new CustomerEntity and you don't set the ContactTitle property to a value, it will not be included in the INSERT query, which will result in a NULL value for ContactTitle in the database table. If a default constraint would have been defined for that field, the RDBMS would have inserted that value instead of NULL.

Back to the list
How do I insert a NULL value for a field in an existing entity?

Because a save action of an existing entity will update the changed fields only, we can't use the mechanism used with saving a new entity. Instead we have to change the value of the field we want to set to NULL to a value, so it is changed, and at the same time the value should then be used to set the field to NULL. Because the properties reflecting the fields are typed, some with value types, we can't set the value typed properties to null/Nothing. Instead we use a method to do that.
Selfservicing / Adapter 
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue((int)CustomerFieldIndex.ContactTitle, null);
// now save the customer. ContactTitle will become NULL.
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
customer.SetNewFieldValue(CType(CustomerFieldIndex.ContactTitle, Integer), Nothing)
' now save the customer. ContactTitle will become NULL.

Instead of a field index, a field name can also be used: 
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue("ContactTitle", null);
// now save the customer. ContactTitle will become NULL.
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
customer.SetNewFieldValue("ContactTitle", Nothing)
' now save the customer. ContactTitle will become NULL.

Back to the list
How do I prevent that new entities are added to an entity collection in a grid?

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.AllowNew property. Setting this property to false/False will assure that there will be no 'new' line in the grid bound to the collection object and the grid is not able to add new entities via the IBindingList interface.

Back to the list
How do I enable sorting functionality of an entity collection in a bound grid?

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.SupportsSorting property and the IBindingList sorting functionality. Setting this property to true/True before the collection is bound to a grid, will make sure that the grid will be able to sort the contents of the collection when the user clicks a column header. Some grid controls offer sorting capabilities without this property, however grids based on .NET's own grid control require this property set to true to enable sorting capabilities.

Back to the list
How do I set a time-out setting of 1 minute using SelfServicing?

You can set a timeout (in seconds) for all commands executed using a global setting in the generated DbUtils class. (HelperClasses namespace). It doesn't hurt to use a timeout larger than what you actually need, so setting it to 1 minute to make sure a long running query will succeed is not degrading performance in your application. If a connection to the database is not available, it will however then take longer to timeout. ADO.NET's default is 15 seconds, the default in the templates is set to 30 seconds. 
// C#
// set it to 60 seconds
DbUtils.CommandTimeOut = 60;
' VB.NET
' set it to 60 seconds
DbUtils.CommandTimeOut = 60

Back to the list
How do I update a series of entities directly in the database?

The example below will update all EmployeeID fields in all orders with the value of 5 where the current EmployeeID field is 2.

   SelfServicing
  • C#
  • VB.NET
// C#
OrderEntity newValues = new OrderEntity();
newValues.EmployeeID = 5;
OrderCollection updater = new OrderCollection();
updater.UpdateMulti(newValues, (OrderFields.EmployeeID == 2));
' VB.NET
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareValuePredicate(OrderFields.EmployeeID, ComparisonOperator.Equal, 2))
Dim updater As New OrderCollection()
updater.UpdateMulti(newValues, filter)

' which is equal to: (VB.NET 2005)
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim updater As New OrderCollection()
updater.UpdateMulti(newValues, (OrderFields.EmployeeID = 2))

   Adapter
  • C#
  • VB.NET
// C#
OrderEntity newValues = new OrderEntity();
newValues.EmployeeID = 5;
IRelationPredicateBucket filter = new RelationPredicateBucket((OrderFields.EmployeeID == 2));
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.UpdateEntitiesDirectly(newValues, filter);
' VB.NET
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New RelationPredicateBucket()
filter.PredicateExpression.Add(New FieldCompareValuePredicate(OrderFields.EmployeeID, Nothing, ComparisonOperator.Equal, 2))
Dim adapter As New DataAccessAdapter()
adapter.UpdateEntitiesDirectly(newValues, filter)

' which is equal to (VB.NET 2005)
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New RelationPredicateBucket( _
	(OrderFields.EmployeeID = 2))
Dim adapter As New DataAccessAdapter()
adapter.UpdateEntitiesDirectly(newValues, filter)

Back to the list
How do I filter on fields in another entity?

Below is an example which retrieves all Customer entities which have bought a given product, in this case the product with ProductID 2

   SelfServicing
  • C#
  • VB.NET
// C#
IRelationCollection relations = new RelationCollection();
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID); 
CustomerCollection customers = new CustomerCollection();
customers.GetMulti((ProductFields.ProductID ==2), relations);
' VB.NET
Dim filter As New PredicateExpression()
Dim relations As New RelationCollection()
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
filter.Add(New FieldCompareValue(ProductFieldIndex.ProductID, ComparisonOperator.Equal, 2))
Dim customers As New CustomerCollection()
customers.GetMulti(filter, relations)

' You also could have defined the filter directly: (VB.NET 2005)
customers.GetMulti((ProductFields.ProductID =2), relations)

   Adapter
  • C#
  • VB.NET
// C#
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID); 
filter.PredicateExpression.Add(ProductFields.ProductID == 2);
EntityCollection customers = new CustomerCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, filter);
' VB.NET
Dim filter As New RelationPredicateBucket()
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
filter.PredicateExpression.Add(New FieldCompareValuePredicate(ProductFields.ProductID, Nothing, ComparisonOperator.Equal, 2))
Dim adapter As New DataAccessAdapter()
Dim customers As New EntityCollection(new CustomerEntityFactory())
adapter.FetchEntityCollection(customers, filter)

' you also could have specified for line A above: (VB.NET 2005)
filter.PredicateExpression.Add(ProductFields.ProductID=2))

Back to the list
How do I handle exceptions which occur during save/delete/fetch actions?

Each exception caught during the execution of the SQL the Dynamic Query Engines generate is wrapped in a ORMQueryExecutionException exception object. You can thus create an extra catch clause which solely catches this exception. The ORMQueryExecutionException exception class embeds the caught exception as the inner exception, the complete SQL executed in text and the parameters collection passed to the query. This ensures you have all the information needed to fully debug the situation.

Back to the list
How do I implement concurrency control?

Concurrency control is something that can be implemented in various ways. Because there are numerous ways to implement concurrency control (abstract concurrency control using functionality locking, low level concurrency schemes with optimistic locking or pessimistic locking, all fields filters, timestamp filters etc.), the generated code offers you the tools to produce the concurrency scheme you want. To implement low level concurrency control like optimistic locking, predicates are used to limit the scope of a query executed. These predicates are added to the query being executed, for example an

To produce these predicates automatically, it's wise to implement IConcurrencyPredicateFactory for the class(es) you want concurrency control for. See the sections about concurrency control (Selfservicing or Adapter) for more details about this.

Back to the list
How do I create a m:n relation between two entity objects?

The generated code will not save intermediate table entries in an m:n relation. Consider the two entities Department (DepartmentID (PK, int, identity), Description) and Employee (EmployeeID (PK, int, identity), Name). These two entities have an m:n relation, and this results in the third entity DepartmentEmployees (DepartmentID (PK), EmployeeID (PK)). To relate a new employee with an existing department follow the example below. It will add a new employee to the existing department with ID 1. You can of course also set the DepartmentID of the DepartmentEmployeesEntity by hand, avoiding the necessity to fetch the DepartmentEntity first.

   SelfServicing
  • C#
  • VB.NET
// C#
DepartmentEntity department = new DepartmentEntity(1);
EmployeeEntity newEmployee = new EmployeeEntity();
newEmployee.Name = "John Doe";
DepartmentEmployeesEntity departmentEmployees = new DepartmentEmployeesEntity();
departmentEmployees.Department = department;
departmentEmployees.Employee = employee;
// save recursively
departmentEmployees.Save(true);
' VB.NET
Dim department As New DepartmentEntity(1)
Dim newEmployee As New EmployeeEntity()
newEmployee.Name = "John Doe"
Dim departmentEmployees As New DepartmentEmployeesEntity()
departmentEmployees.Department = department
departmentEmployees.Employee = employee
' save recursively
departmentEmployees.Save(True)

   Adapter
  • C#
  • VB.NET
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
DepartmentEntity department = new DepartmentEntity(1);
adapter.FetchEntity(department);
EmployeeEntity newEmployee = new EmployeeEntity();
newEmployee.Name = "John Doe";
DepartmentEmployeesEntity departmentEmployees = new DepartmentEmployeesEntity();
departmentEmployees.Department = department;
departmentEmployees.Employee = employee;
// save recursively
adapter.SaveEntity(departmentEmployees);
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim department As New DepartmentEntity(1)
adapter.FetchEntity(department)
Dim newEmployee As New EmployeeEntity()
newEmployee.Name = "John Doe"
Dim departmentEmployees As New DepartmentEmployeesEntity()
departmentEmployees.Department = department
departmentEmployees.Employee = employee
' save recursively
adapter.SaveEntity(departmentEmployees)

Back to the list
How do I fetch a typed view or typed list in a transaction using SelfServicing (SqlServer)?

It can be necessary to fetch a typed view or typed list during a transaction. Because in SqlServer, writers block readers, it is required that the fetch of the typed list or typed view is done using the same connection as the transaction uses. However typed lists and typed views are not addable to a Transaction object, like entities and entity collection classes are. Typed lists and typed views have a Fill() method overload which accepts a Transaction object. Use that overload to pass an existing transaction object and the Fill() method will use the connection of the passed in transaction to retrieve the data requested instead of using a new connection. This avoids deadlocks because the writers used in the transaction block a read (fetch) by the Fill() method.

Back to the list
How do I speed up manual bulk additions to entity collection objects?

If you manually add entities to an EntityCollection object (adapter) or EntitynameCollection (SelfServicing) object, you'll run into a performance penalty as the Add() method will check wether the entity added is already in the collection or not. This can lead to severe slowness in an application if the collection already holds a lot of entity objects (Add() uses List.Contains() which is a .NET method and which performs a linear search). To prevent this performance penalty, for example if you don't care if an entity object is added twice, you can set the EntityCollection (Adapter) or EntitynameCollection (SelfServicing) property DoNotPerformAddIfPresent to false. When you do that, all Add() calls will skip checking if an entity is already in the collection and will be significant faster. FetchEntityCollection() always sets this property to false before fetching new entities in the collection.

Back to the list
How do I sort the fields in a typed list?

Sorting a typed list is similar to sorting the objects in an entity collection. Say we have a typed list with the fields of 'Customer' and 'Order', and we want to sort the data on 'OrderDate' descending and 'CompanyName' ascending. First we define the sortexpression. This is the same for SelfServicing and Adapter.

Selfservicing / Adapter 
// C#
SortExpression sorter = 
	(OrderFields.OrderDate | SortOperator.Descending) & (CustomerFields.CompanyName | SortOperator.Ascending);
' VB.NET
Dim sorter As ISortExpression = New SortExpression()
' SelfServicing
sorter.Add(New SortClause(OrderFields.OrderDate, SortOperator.Descending))
sorter.Add(New SortClause(CustomerFields.CompanyName, SortOperator.Ascending))
' Adapter
sorter.Add(New SortClause(OrderFields.OrderDate, Nothing, SortOperator.Descending))
sorter.Add(New SortClause(CustomerFields.CompanyName, Nothing, SortOperator.Ascending))

' which is equal to (VB.NET 2005)
Dim sorter As SortExpression = _
	(OrderFields.OrderDate OR SortOperator.Descending) AND (CustomerFields.CompanyName OR SortOperator.Ascending)

This sortexpression can now be passed as the sort expression in a typed list fetch. Below are the typed list fetch statements using SelfServicing and Adapter. Our typed list is called OrderCustomerTypedList.

   Selfservicing
  • C#
  • VB.NET
// C#
OrderCustomerTypedList orderCustomerData = new OrderCustomerTypedList();
orderCustomerData.Fill(0, sorter, true, null);
' [VB.NET]
Dim orderCustomerData As New OrderCustomerTypedList()
orderCustomerData.Fill(0, sorter, True, Nothing)

   Adapter
  • C#
  • VB.NET
// C#
OrderCustomerTypedList orderCustomerData = new OrderCustomerTypedList();
adapter.FetchTypedList(orderCustomerData.GetFieldsInfo(), orderCustomerData, 
	orderCustomerData.GetRelationInfo(), 0, sorter, true);
' [VB.NET]
Dim orderCustomerData As New OrderCustomerTypedList()
adapter.FetchTypedList(orderCustomerData.GetFieldsInfo(), orderCustomerData, _
	orderCustomerData.GetRelationInfo(), 0, sorter, True)

Back to the list
How do I write a filter which does WHERE field = 3 ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == 3);
' VB.NET
Dim filter As New PredicateExpression()
' SelfServicing:
filter.Add(New FieldCompareValuePredicate(YourEntityFields.Field, ComparisonOperator.Equal, 3))
' Adapter:
filter.Add(New FieldCompareValuePredicate(YourEntityFields.Field, Nothing, ComparisonOperator.Equal, 3))

' which is equal to: (VB.NET 2005)
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field = 3)

Back to the list
How do I write a filter which does WHERE fielda = fieldb ?

Field compares are done using a FieldCompareExpressionPredicate class. 
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(OrderFields.OrderDate == OrderFields.ShippingDate);
' VB.NET
Dim filter As New PredicateExpression()
' SelfServicing
filter.Add(New FieldCompareExpressionPredicate(OrderFields.OrderDate, 
	ComparisonOperator.Equal, _
	New Expression(OrderFields.ShippingDate)))
' Adapter:
filter.Add(New FieldCompareExpressionPredicate(OrderFields.OrderDate, 
	Nothing, ComparisonOperator.Equal, _
	New Expression(OrderFields.ShippingDate)))
	
' Which is equal to (VB.NET 2005)
Dim filter As New PredicateExpression()
filter.Add(OrderFields.OrderDate = OrderFields.ShippingDate)

Back to the list
How do I write a filter which does WHERE field LIKE '%foo%' ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field % "%Foo%");
' VB.NET
Dim filter As New PredicateExpression()
' SelfServicing
filter.Add(New FieldLikePredicate(YourEntityFields.Field, "%foo%"))
' Adapter
filter.Add(New FieldLikePredicate(YourEntityFields.Field, Nothing, "%foo%"))

' which is equal to (VB.NET 2005)
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field Mod "%Foo%")

Back to the list
How do I write a filter which does WHERE field BETWEEN 1 AND 10 ?

   SelfServicing
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(YourEntityFields.Field, 1, 10));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldBetweenPredicate(YourEntityFields.Field, 1, 10))

   Adapter
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(YourEntityFields.Field, Null, 1, 10));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldBetweenPredicate(YourEntityFields.Field, Nothing, 1, 10))

Back to the list
How do I write a filter which does WHERE field IN (1, 2, 5) ?

There are several ways to specify a range of fixed values: in an Array, in any IList implementing class like ArrayList or List<T> and directly in the call to the predicate creation. All three ways are shown below.

Using an array
// C#
IPredicateExpression filter = new PredicateExpression();
int[] values = new int[3] {1, 2, 5};
filter.Add(YourEntityFields.Field == values);
' VB.NET
Dim filter As New PredicateExpression()
Dim values As Integer() = New Integer(2) {1, 2, 5}
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, values))
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, values))

Using an IList implementing class, e.g. ArrayList
// C#
IPredicateExpression filter = new PredicateExpression();
ArrayList values = new ArrayList();
values.Add(1);
values.Add(2);
values.Add(5);
filter.Add(YourEntityFields.Field == values);
' VB.NET
Dim filter As New PredicateExpression()
Dim values As New ArrayList()
values.Add(1)
values.Add(2)
values.Add(5)
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, values))
' Adapter
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, values))

Using values directly in the call
   SelfServicing
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareRangePredicate(YourEntityFields.Field, 1, 2, 5));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, 1, 2, 5))

   Adapter
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareRangePredicate(YourEntityFields.Field, Nothing, 1, 2, 5));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, 1, 2, 5))

Back to the list
How do I write a filter which does WHERE field IN (SELECT fieldb FROM foo) ?

   SelfServicing
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
	YourEntityFields.Field, FooFields.Fieldb, SetOperator.In, null);
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(new FieldCompareSetPredicate( _
	YourEntityFields.Field, FooFields.Fieldb, _
	SetOperator.In, Nothing))

   Adapter
  • C#
  • VB.NET
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
	YourEntityFields.Field, null, FooFields.Fieldb, null, 
	SetOperator.In, null));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(new FieldCompareSetPredicate( _
	YourEntityFields.Field, Nothing, FooFields.Fieldb, Nothing, _
	SetOperator.In, Nothing))

Back to the list
How do I write a filter which does WHERE field IS NULL ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == DBNull.Value));
' VB.NET
Dim filter As New PredicateExpression()
' SelfServicing
filter.Add(New FieldCompareNullPredicate(YourEntityFields.Field))
' Adapter
filter.Add(New FieldCompareNullPredicate(YourEntityFields.Field, Nothing))

' which is equal to (VB.NET 2005)
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field = DBNull.Value))

Back to the list
How do I use a group by clause in a dynamic list ?

The following query formulates a dynamic list with all managers and the number of employees which are reporting to them. The query is ran on Northwind, which contains a self-referencing Employees table.

   SelfServicing
  • C#
  • VB.NET
// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
RelationCollection relations = new RelationCollection();
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

TypedListDAO dao = new TypedListDAO();
DataTable tlist = new DataTable();
dao.GetMultiAsDataTable(fields, tlist, 0, null, null, relations, true, groupByClause, null, 0, 0);
' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count)
Dim relations As New RelationCollection()
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None)
Dim groupByClause As New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))

Dim dao As New TypedListDAO()
Dim tlist As New DataTable()
dao.GetMultiAsDataTable(fields, tlist, 0, Nothing, Nothing, relations, True, groupByClause, Nothing, 0, 0)

   Adapter
  • C#
  • VB.NET
// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

DataAccessAdapter adapter = new DataAccessAdapter();
DataTable tlist = new DataTable();
adapter.FetchTypedList(fields, tlist, bucket, 0, null, true, groupByClause);
' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count)
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None)
Dim groupByClause As New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))

Dim adapter As New DataAccessAdapter()
Dim tlist As New DataTable()
adapter.FetchTypedList(fields, tlist, bucket, 0, Nothing, True, groupByClause)

Back to the list
How do I sort a filled entity collection ?

Sorting an entity collection client-side (i.e.: outside the database), can be done using the Sort() method implemented in the entity collections. There are various overloads. Two are shown below: sorting by specifying a property name and one using an entity field index and a custom comparer. The sorting is done on a filled collection of CustomerEntity objects, called customers.

Sorting by specifying a property name
The property name can be any property, also a property you've added in code, it doesn't have to be an entity field.

   SelfServicing
  • C#
  • VB.NET
// C#. 
EntityView view = new EntityView(customers);
view.Sorter = (new EntityProperty("propertyName") | SortOperator.Ascending);
' VB.NET
Dim view As New EntityView(customers)
view.Sorter = New SortExpression(New SortClause(New EntityProperty("propertyName"), Nothing, SortOperator.Ascending))

   Adapter
  • C#
  • VB.NET
// C#. 
EntityView2 view = new EntityView2(customers);
view.Sorter = (new EntityProperty("propertyName") | SortOperator.Ascending);
' VB.NET
Dim view As New EntityView2(customers)
view.Sorter = New SortExpression(New SortClause(New EntityProperty("propertyName"), Nothing, SortOperator.Ascending))

Back to the list
How do I get the MAX(Order.ShippingDate - Order.OrderDate) value from a customer's orders ?

   SelfServicing
  • C#
  • VB.NET
// C#
OrderCollection orders = new OrderCollection();
int maxValue = (int)orders.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate),
	AggregateFunction.Max, (OrderFields.CustomerId == _customerId));
' VB.NET 
Dim orders As New OrderCollection()
Dim filter As IPredicate = New FieldCompareValuePredicate(OrderFields.CustomerId, ComparisonOperator.Equal, _customerId)
Dim subExpression As New Expression(OrderFields.ShippedDate, ExOp.Sub, OrderFields.OrderDate)
Dim maxValue = CInt(orders.GetScalar(OrderFieldIndex.OrderId, subExpression, AggregateFunction.Max, filter))

' which is equal to (VB.NET 2005)
Dim orders As New OrderCollection()
Dim maxValue = CInt(orders.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), _
	AggregateFunction.Max, (OrderFields.CustomerId = _customerId)))

   Adapter
  • C#
  • VB.NET
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
int maxValue = (int)adapter.GetScalar(OrderFields.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), 
	AggregateFunction.Max, (OrderFields.CustomerId == _customerId));
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim filter As IPredicate = New FieldCompareValuePredicate(OrderFields.CustomerId, Nothing, ComparisonOperator.Equal, _customerId)
Dim subExpression As New Expression(OrderFields.ShippedDate, ExOp.Sub, OrderFields.OrderDate)
Dim maxValue As Integer = CInt(adapter.GetScalar(OrderFieldIndex.OrderId, subExpression, _
	AggregateFunction.Max, filter))
	
' Which is equal to (VB.NET 2005)
Dim adapter As New DataAccessAdapter()
Dim maxValue As Integer = CInt(adapter.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), _
	AggregateFunction.Max, (OrderFields.CustomerId == _customerId)))

Back to the list
How do I construct a multi-node prefetch path and specify sorting for one node ?

   SelfServicing
  • C#
  • VB.NET
// C#
IPrefetchPath path = new PrefetchPath((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As New PrefetchPath(CInt(EntityType.EmployeeEntity))
Dim sorter As New SortExpression()
sorter.Add(New SortClause(OrderFields.OrderDate, SortOperator.Descending))
path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers)
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders)

   Adapter
  • C#
  • VB.NET
// C#
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As New PrefetchPath2(CInt(EntityType.EmployeeEntity))
Dim sorter As New SortExpression()
sorter.Add(New SortClause(OrderFields.OrderDate, Nothing, SortOperator.Descending))
path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers)
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders)

Another way to add the Orders and the Shippers nodes is the following. This example allows you to add more nodes to the Orders node.

   Selfservicing
  • C#
  • VB.NET
// C# 
IPrefetchPathElement ordersElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts); // you can now add this subnode
' VB.NET
Dim ordersElement As IPrefetchPathElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts) ' you can now add this subnode

   Adapter
  • C#
  • VB.NET
// C#
IPrefetchPathElement2 ordersElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts); // you can now add this subnode
' VB.NET
Dim ordersElement As IPrefetchPathElement2 = path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts) ' you can now add this subnode

Back to the list
How do I add an entity A to an entity B's collection of A's if A and B have an m:n relation ?

Say you want to add an Employee to a Department and Employee and Department have an m:n relation. This means that there is an intermediate entity DepartmentEmployees which defines the m:n relation with a foreign key to both Employee and Department. Employee has a collection Departments which results in all Department entities the Employee works for and Department has a collection Employees which results in all Employees working for the Department. In LLBLGen Pro, m:n relations are read-only, therefore, to add an Employee to a Department, you've to use the following steps. The example uses department and employee which are filled in DepartmentEntity and EmployeeEntity instances. You can also use new entities, this doesn't matter.

   Selfservicing
  • C#
  • VB.NET
// C#
DepartmentEmployees newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
// to save the relation in the database.
employee.Save(true);
' VB.NET
Dim newDepEmp As New DepartmentEmployees()
newDepEmp.Employee = employee
newDepEmp.Department = department
' the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
' to save the relation in the database.
employee.Save(True)

   Adapter
  • C#
  • VB.NET
// C#
DepartmentEmployees newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
// to save the relation in the database.
adapter.SaveEntity(employee);
' VB.NET
Dim newDepEmp As New DepartmentEmployees()
newDepEmp.Employee = employee
newDepEmp.Department = department
' the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
' to save the relation in the database.
adapter.SaveEntity(employee)

Keep in mind that the m:n relation is then saved in the database, but the situation in memory isn't updated, i.e.: employee.Departments doesn't contain 'department'. This is by design, as the m:n relation is based on a 3rd entity, which can be altered by any thread in the system, making the m:n relation, which is a result of the values of the intermediate entity, a 'view' of a situation at a given point in time.
Back to the list
LLBLGen Pro v2.6 documentation. ©2002-2008 Solutions Design